#include <QCoreApplication>
#include "sqlconnectionpool/SqlConnectionPool.h"
#include "xlsxdocument.h"
#include <QDebug>
#include <QMap>
#include <QList>
#include <QtNetwork>
#include <QJsonObject>

void initDB();
QList<QMap<QString, QString>> readXMLData(const QString &xmlFile);
void saveXMLData(const QList<QMap<QString, QString>> &data);
void updateId();
void updateGroupDeviceId();
void syncChData();
void saveNoGroupDevice();
void updateGroupDevice();
void convertLocation2Address();

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    initDB();
    QList<QMap<QString, QString>> data = readXMLData("/Users/apple/Documents/项目/廊坊消防/gps/数据/张家口/北斗总表的副本.xlsx");
    saveXMLData(data);
//    syncChData();
//    saveNoGroupDevice();
//    updateGroupDevice();
//    convertLocation2Address();
    qDebug() << "end";

    return a.exec();
}

void initDB()
{
//    SqlConnectionPool::instance(1, "QMYSQL", "gpscar", "root", "123456", "127.0.0.1", 3306);
    SqlConnectionPool::instance(1, "QMYSQL", "gpscar", "root", "Bjht12345678@", "server.natappfree.cc", 39484);

    QString sql = "select 1";
    bool isOk = false;
    SqlConnectionPool::instance()->query(sql, &isOk, false);
    if (isOk) {
        qDebug("connect db success");
    } else {
        qDebug("connect db failure");
    }
}

QList<QMap<QString, QString>> readXMLData(const QString &xmlFile)
{
    QList<QMap<QString, QString>> result;

    QXlsx::Document xlsx(xmlFile);

    if (!xlsx.isLoadPackage()) {
        qDebug() << "file not exist" << xmlFile;
        return result;
    }

    for (QString name : xlsx.sheetNames()) {
        // 遍历sheet
        xlsx.selectSheet(name);

        int rowIndex = 3;
        while (1) {
            if (xlsx.cellAt(rowIndex, 3) == nullptr) {
                break;
            }
            if (xlsx.cellAt(rowIndex, 3)->readValue().toString().trimmed().isEmpty()) {
                break;
            }
            // 判断是否包含大队
            bool hasDD = true;
            if (xlsx.cellAt(rowIndex, 7) == nullptr || xlsx.cellAt(rowIndex, 7)->value().toString().trimmed().isEmpty()) {
                hasDD = false;
            }

            int startIndex = 2;
            // 支队名称
            QString zdname = xlsx.cellAt(rowIndex, startIndex)->readValue().toString().trimmed();

            // 大队名称
            QString ddname;
            if (hasDD) {
                ddname = xlsx.cellAt(rowIndex, ++startIndex)->readValue().toString().trimmed();
            }

            // 车牌号
            QString plate = xlsx.cellAt(rowIndex, ++startIndex)->readValue().toString();
            // 设备id
            QString device = xlsx.cellAt(rowIndex, ++startIndex)->readValue().toString();
            // 车辆用途
            QString type = xlsx.cellAt(rowIndex, (startIndex + 2))->readValue().toString();

            if (device.trimmed().isEmpty()) {
                rowIndex++;
                continue;
            }

            QMap<QString, QString> item;
            item.insert("zdname", zdname);
            if (!ddname.isEmpty()) {
                item.insert("ddname", ddname);
            }
            item.insert("plate", plate);
            item.insert("device", device);
            item.insert("type", type);
            result.append(item);

            qDebug() << zdname << ddname << plate << device << type;

            rowIndex++;
        }

        break;
    }

    return result;
}

void saveXMLData(const QList<QMap<QString, QString>> &data)
{
    foreach (const auto &item, data) {
        int groupId = 0;
        QVector<QVariant> args;

        // 查找支队
        QString findZdSql = "select * from tgps_group where name = '" + item.value("zdname") + "'";
        bool isOk = false;
        SqlResult result = SqlConnectionPool::instance()->query(findZdSql, &isOk, false);
        if (result.records().size() == 0) {
            // 插入支队
            QString insertZdSql = "insert into tgps_group (name, fid, state, orderindex, type)"
                                    " values (:name, 0, 'open', 10, 1)";
            args.clear();
            args.append(item.value("zdname"));
            result = SqlConnectionPool::instance()->query(insertZdSql, &args, &isOk, false);
            groupId = result.lastInsertId().toInt();
            qDebug() << "insert zd" << item.value("device");
        } else {
            groupId = result.records()[0].value("id").toInt();
        }

        // 查找大队
        if (item.contains("ddname")) {
            if (groupId == 0) {
                qDebug() << "dd process but zd empty" << item.value("device");
                continue;
            }
            QString findDdSql = "select * from tgps_group where name = '" + item.value("ddname") + "'";
            SqlResult result = SqlConnectionPool::instance()->query(findDdSql, &isOk, false);
            if (result.records().size() == 0) {
                // 插入大队
                QString insertDdSql = "insert into tgps_group (name, fid, state, orderindex, type)"
                                        " values (:name, :fid, 'open', 10, 1)";
                args.clear();
                args.append(item.value("ddname"));
                args.append(groupId);
                result = SqlConnectionPool::instance()->query(insertDdSql, &args, &isOk, false);
                groupId = result.lastInsertId().toInt();
                qDebug() << "insert dd" << item.value("device");
            } else {
                groupId = result.records()[0].value("id").toInt();
            }
        }

        // 查找设备
        QString deviceId = item.value("device");
        QString plate = item.value("plate");
        if (plate == "无" || plate == "正在办理中" || plate == "正在办理" || plate == "暂未上牌" || plate == "暂无车牌" || plate.trimmed().isEmpty()) {
            plate = deviceId;
        }
        QString findDeviceSql = "select * from tgps_car where id like '%" + deviceId + "'";
        result = SqlConnectionPool::instance()->query(findDeviceSql, &isOk, false);
        if (result.records().size() == 0) {
            QString insertDeviceSql = "insert into tgps_car (id, sim, zdid, carno, pinpai, stime, etime, create_time, modify_time, limit_speed)"
                                    " values (:id, :sim, :zdid, :carno, :pinpai, :stime, :etime, :create_time, :modify_time, 120)";
            args.clear();
            args.append(deviceId);
            args.append(deviceId);
            args.append(deviceId);
            args.append(plate);
            args.append(item.value("type"));
            args.append(QDateTime::currentDateTime().toString("yyyy-MM-dd"));
            args.append(QDateTime::currentDateTime().addYears(10).toString("yyyy-MM-dd"));
            args.append(QDateTime::currentDateTime().toString("yyyyMMddHHmmss"));
            args.append(QDateTime::currentDateTime().toString("yyyyMMddHHmmss"));
            result = SqlConnectionPool::instance()->query(insertDeviceSql, &args, &isOk, false);
            if (!result.error().text().isEmpty()) {
                qDebug() << "insert device error" << deviceId << result.error().text();
                continue;
            }
            qDebug() << "insert device" << item.value("device");
        } else {
            // 更新
            QString updateDeviceSql = "update tgps_car set carno = :carno, pinpai = :pinpai where id = :id";
            args.clear();
            args.append(plate);
            args.append(item.value("type"));
            args.append(deviceId);
            result = SqlConnectionPool::instance()->query(updateDeviceSql, &args, &isOk, false);
            if (!result.error().text().isEmpty()) {
                qDebug() << "update device error" << deviceId << result.error().text();
                continue;
            }
            qDebug() << "update device" << item.value("device");
        }

        // 给设备分组
        QString findGroupDeviceSql = "select * from tgps_group_car where car_id = :id";
        args.clear();
        args.append(deviceId);
        result = SqlConnectionPool::instance()->query(findGroupDeviceSql, &args, &isOk, false);
        if (result.records().size() == 0) {
            QString insertGroupDeviceSql = "insert into tgps_group_car (car_id, group_id) values (:car_id, :group_id)";
            args.clear();
            args.append(deviceId);
            args.append(groupId);
            result = SqlConnectionPool::instance()->query(insertGroupDeviceSql, &args, &isOk, false);
            if (!result.error().text().isEmpty()) {
                qDebug() << "insert device group error" << deviceId << result.error().text();
                continue;
            }
            qDebug() << "insert device group" << item.value("device");
        } else {
            int gid = result.records()[0].value("groupId").toInt();
            if (gid != groupId) {
                QString updateGroupDeviceSql = "update tgps_group_car set group_id = :group_id where car_id = :id";
                args.clear();
                args.append(groupId);
                args.append(deviceId);
                result = SqlConnectionPool::instance()->query(updateGroupDeviceSql, &args, &isOk, false);
                if (!result.error().text().isEmpty()) {
                    qDebug() << "update device group error" << deviceId << result.error().text();
                    continue;
                }
            }
        }
    }
}

void syncChData()
{
    // 查找创辉数据
    QList<QMap<QString, QString>> chDevices;
    QString findChDeviceSql = "select * from ch_vehicle";
    bool isOk = false;
    SqlResult result = SqlConnectionPool::instance()->query(findChDeviceSql, &isOk, false);
    if (result.records().size() > 0) {
        for (int i = 0; i < result.records().size(); i++) {
            QMap<QString, QString> item;
            item.insert("zdname", "廊坊市消防救援支队");
            item.insert("type", "消防车");
            item.insert("ddname", result.records()[i].value("XFJGJC").toString());
            item.insert("device", result.records()[i].value("GPSID").toString());
            QString plate = result.records()[i].value("CPHM").toString();
            item.insert("plate", plate.isEmpty() ? item.value("device") : plate);
            chDevices.append(item);
        }

    } else {
        qDebug() << "find ch device empty";
        return;
    }

    foreach (const auto &item, chDevices) {
        // 插入车辆
        QVector<QVariant> args;
        QString insertDeviceSql = "insert into tgps_car (id, sim, zdid, carno, pinpai, stime, etime, create_time, modify_time)"
                                " values (:id, :sim, :zdid, :carno, :pinpai, :stime, :etime, :create_time, :modify_time)";
        args.clear();
        args.append(item.value("device"));
        args.append(item.value("device"));
        args.append(item.value("device"));
        args.append(item.value("plate"));
        args.append(item.value("type"));
        args.append(QDateTime::currentDateTime().toString("yyyy-MM-dd"));
        args.append(QDateTime::currentDateTime().addYears(10).toString("yyyy-MM-dd"));
        args.append(QDateTime::currentDateTime().toString("yyyyMMddHHmmss"));
        args.append(QDateTime::currentDateTime().toString("yyyyMMddHHmmss"));
        result = SqlConnectionPool::instance()->query(insertDeviceSql, &args, &isOk, false);
        if (!result.error().text().isEmpty()) {
            qDebug() << "insert device error" << item.value("device") << result.error().text();
            continue;
        }
        qDebug() << "insert device" << item.value("device");

        // 插入分组
        int groupId = 147;
        QString findGroupSql = "select * from tgps_group where name = :name";
        args.clear();
        args.append(item.value("ddname"));
        result = SqlConnectionPool::instance()->query(findGroupSql, &args, &isOk, false);
        if (!result.records().empty()) {
            groupId = result.records()[0].value("id").toInt();
        }

        QString insertGroupDeviceSql = "insert into tgps_group_car (group_id, car_id)"
                                        " values (:group_id, :car_id)";
        args.clear();
        args.append(groupId);
        args.append(item.value("device"));
        result = SqlConnectionPool::instance()->query(insertGroupDeviceSql, &args, &isOk, false);
        if (!result.error().text().isEmpty()) {
            qDebug() << "insert device group error" << item.value("device") << result.error().text();
        } else {
            qDebug() << "insert device group" << item.value("device");
        }
    }
}

void updateId()
{
    QString findDeviceSql = "select * from tgps_car";
    bool isOk = false;
    SqlResult result = SqlConnectionPool::instance()->query(findDeviceSql, &isOk, false);
    if (result.records().size() > 0) {
        for (int i = 0, n = result.records().size(); i < n; i++) {
            QString id = result.records()[i].value("id").toString();
            if (id.length() > 12 && id.length() < 20) {
                QString newId = QString("000000000").mid(0, 20 - id.length()) + id;
                QString updateDeviceSql = "update tgps_car set id = :newId, sim = :sim, zdid = :zdid where id = :id";
                QVector<QVariant> args;
                args.append(newId);
                args.append(newId);
                args.append(newId);
                args.append(id);
                SqlResult result1 = SqlConnectionPool::instance()->query(updateDeviceSql, &args, &isOk, false);
                if (!result1.error().text().isEmpty()) {
                    qDebug() << "update device id error" << id << newId << result1.error().text();
                } else {
                    qDebug() << "update device id" << id << newId;
                }
            }
        }
    }
}

void updateGroupDeviceId()
{
    QString findDeviceSql = "select * from tgps_group_car";
    bool isOk = false;
    SqlResult result = SqlConnectionPool::instance()->query(findDeviceSql, &isOk, false);
    if (result.records().size() > 0) {
        for (int i = 0, n = result.records().size(); i < n; i++) {
            QString id = result.records()[i].value("car_id").toString();
            if (id.length() > 12 && id.length() < 20) {
                QString newId = QString("000000000").mid(0, 20 - id.length()) + id;
                QString updateDeviceSql = "update tgps_group_car set car_id = :newId where car_id = :id";
                QVector<QVariant> args;
                args.append(newId);
                args.append(id);
                SqlResult result1 = SqlConnectionPool::instance()->query(updateDeviceSql, &args, &isOk, false);
                if (!result1.error().text().isEmpty()) {
                    qDebug() << "update group device id error" << id << newId << result1.error().text();
                } else {
                    qDebug() << "update group device id" << id << newId;
                }
            }
        }
    }
}

void saveNoGroupDevice()
{
    QList<QMap<QString, QString>> result;

    QXlsx::Document xlsx("/Users/apple/Desktop/Untitled.xlsx");

    if (!xlsx.isLoadPackage()) {
        qDebug() << "file not exist";
        return;
    }

    for (QString name : xlsx.sheetNames()) {
        // 遍历sheet
        xlsx.selectSheet(name);

        int rowIndex = 1;
        while (1) {
            if (xlsx.cellAt(rowIndex, 1) == nullptr) {
                break;
            }
            if (xlsx.cellAt(rowIndex, 1)->readValue().toString().trimmed().isEmpty()) {
                break;
            }

            QString device = xlsx.cellAt(rowIndex, 1)->readValue().toString();

            QString insertGroupDeviceSql = "insert into tgps_group_car (group_id, car_id)"
                                            " values (:group_id, :car_id)";
            bool isOk = false;
            QVector<QVariant> args;
            args.append(147);
            args.append(device);
            SqlResult result = SqlConnectionPool::instance()->query(insertGroupDeviceSql, &args, &isOk, false);
            rowIndex++;
        }
    }
}

void updateGroupDevice()
{
    QXlsx::Document xlsx("/Users/apple/Documents/项目/廊坊消防/gps/数据/创辉/改/保定定位设备.xlsx");

    if (!xlsx.isLoadPackage()) {
        qDebug() << "file not exist";
        return;
    }

    for (QString name : xlsx.sheetNames()) {
        // 遍历sheet
        xlsx.selectSheet(name);

        int rowIndex = 2;
        while (1) {
            if (xlsx.cellAt(rowIndex, 1) == nullptr) {
                break;
            }
            if (xlsx.cellAt(rowIndex, 1)->readValue().toString().trimmed().isEmpty()) {
                break;
            }

            QString group = xlsx.cellAt(rowIndex, 1)->readValue().toString();
            QString device = xlsx.cellAt(rowIndex, 2)->readValue().toString();
            device = device.mid(3, 12);

            bool isOk = false;
            QVector<QVariant> args;
            // 查找分组
            QString findGroupDeviceSql = "select * from tgps_group_car where car_id = :car_id";
            args.clear();
            args.append(device);
            SqlResult result = SqlConnectionPool::instance()->query(findGroupDeviceSql, &args, &isOk, false);
            if (result.records().size() > 0) {
                int groupId = result.records()[0].value("group_id").toInt();
                int groupDeivceId = result.records()[0].value("id").toInt();
                qDebug() << "find group device" << groupId << groupDeivceId;
                if (groupId == 147) {
                    // 更新分组
                    // 查找新的分组ID
                    QString findNewGroupSql = "select * from tgps_group where name = :name";
                    args.clear();
                    args.append(group);
                    result = SqlConnectionPool::instance()->query(findNewGroupSql, &args, &isOk, false);
                    if (result.records().size() > 0) {
                        groupId = result.records()[0].value("id").toInt();
                        QString updateNewGroupSql = "update tgps_group_car set group_id = :group_id where id = :id";
                        args.clear();
                        args.append(groupId);
                        args.append(groupDeivceId);
                        result = SqlConnectionPool::instance()->query(updateNewGroupSql, &args, &isOk, false);
                        if (isOk) {
                            qDebug() << "update group device" << device << groupId;
                        } else {
                            qDebug() << "!!!!" << "update group device error" << device << groupId << result.error().text();
                        }
                    } else {
                        qDebug() << "!!!!" << "new group not find" << group;
                    }
                }
            } else {
                // 插入车辆
                QString insertDeviceSql = "insert into tgps_car (id, sim, zdid, carno, pinpai, stime, etime, create_time, modify_time)"
                                        " values (:id, :sim, :zdid, :carno, :pinpai, :stime, :etime, :create_time, :modify_time)";
                args.clear();
                args.append(device);
                args.append(device);
                args.append(device);
                args.append(device);
                args.append("消防车");
                args.append(QDateTime::currentDateTime().toString("yyyy-MM-dd"));
                args.append(QDateTime::currentDateTime().addYears(10).toString("yyyy-MM-dd"));
                args.append(QDateTime::currentDateTime().toString("yyyyMMddHHmmss"));
                args.append(QDateTime::currentDateTime().toString("yyyyMMddHHmmss"));
                result = SqlConnectionPool::instance()->query(insertDeviceSql, &args, &isOk, false);
                if (!result.error().text().isEmpty()) {
                    qDebug() << "insert device error" << device << result.error().text();
                    rowIndex++;
                    continue;
                }

                qDebug() << "insert device" << device;

                // 查找新的分组ID
                QString findNewGroupSql = "select * from tgps_group where name = :name";
                args.clear();
                args.append(group);
                result = SqlConnectionPool::instance()->query(findNewGroupSql, &args, &isOk, false);
                if (result.records().size() > 0) {
                    int groupId = result.records()[0].value("id").toInt();
                    QString insertNewGroupSql = "insert into tgps_group_car (group_id, car_id) values (:group_id, :car_id)";
                    args.clear();
                    args.append(groupId);
                    args.append(device);
                    result = SqlConnectionPool::instance()->query(insertNewGroupSql, &args, &isOk, false);
                    if (isOk) {
                        qDebug() << "intsert new group device" << device << groupId;
                    } else {
                        qDebug() << "!!!!" << "insert group device error" << device << groupId << result.error().text();
                    }
                }
            }

            rowIndex++;
        }
    }
}

void convertLocation2Address()
{
    QXlsx::Document xlsx("/Users/apple/Desktop/find_location_147_device.xlsx");

    if (!xlsx.isLoadPackage()) {
        qDebug() << "file not exist";
        return;
    }

    for (QString name : xlsx.sheetNames()) {
        // 遍历sheet
        xlsx.selectSheet(name);

        int rowIndex = 1;
        while (1) {
            if (xlsx.cellAt(rowIndex, 1) == nullptr) {
                break;
            }
            if (xlsx.cellAt(rowIndex, 1)->readValue().toString().trimmed().isEmpty()) {
                break;
            }

            QString device = xlsx.cellAt(rowIndex, 1)->readValue().toString();
            double lat = xlsx.cellAt(rowIndex, 2)->readValue().toDouble();
            double lon = xlsx.cellAt(rowIndex, 3)->readValue().toDouble();

            QString url = QString("https://restapi.amap.com/v3/geocode/regeo?output=json&key=861089faa8611ac3b8fcfe5a96afaf6b&location=%1,%2").arg(lon).arg(lat);
            QNetworkAccessManager net;
            QNetworkRequest request(QUrl(url.toLocal8Bit()));
            QNetworkReply *reply = net.get(request);
            QEventLoop ev;
            QObject::connect(reply, SIGNAL(finished()), &ev, SLOT(quit()));
            ev.exec(QEventLoop::ExcludeUserInputEvents);
            if (reply->error() == QNetworkReply::NoError) {
                QByteArray ba = reply->readAll();
                QJsonParseError jsonpe;
                QJsonDocument json = QJsonDocument::fromJson(ba, &jsonpe);
                if (jsonpe.error == QJsonParseError::NoError) {
                    if (json.isObject()) {
                        QJsonObject obj = json.object();
                        if (obj["status"].toString() == "1") {
                            qDebug() << device
                                     << url
                                     << obj["regeocode"].toObject()["formatted_address"].toString();
                        } else {
                            qDebug() << "http status error";
                        }
                    } else {
                        qDebug() << "http error" << device;
                    }
                } else {
                    qDebug() << "http error:" << device << jsonpe.errorString();
                }
            } else {
                qDebug() << "http error:" << device << reply->errorString();
            }
            reply->close();
            reply->deleteLater();

            rowIndex++;
        }
    }
}
